﻿using MSharp.Data.DatabaseInfo;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Collections.Concurrent;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
using System.Text.RegularExpressions;

namespace MSharp.Data.Database
{
    public class SqlServerDB : DB
    {

        public SqlServerDB(DBType dbType, DbProviderFactory dbFactory, string ConnectionString)
            : base(dbType, dbFactory, ConnectionString)
        {
            this.Info = new SqlServerDBInfo(this);
        }

        public override bool ValidateSql(string strSql, out Exception ex)
        {
            bool bResult = false;
            ex = null;
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                conn.Open();
                try
                {
                    cmd.CommandText = "set noexec on;";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "set noexec off;";
                    cmd.ExecuteNonQuery();
                    bResult = true;
                }
                catch (Exception e)
                {
                    ex = e;
                    bResult = false;
                }
                finally
                {
                    cmd?.Dispose();
                }
            }
            return bResult;
        }

        public override DataTable GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string whereStr, string orderbyStr, out long totalCount)
        {
            if (string.IsNullOrEmpty(selColumns))
            {
                selColumns = "*";
            }

            if (currentPage <= 0)
            {
                currentPage = 1;
            }

            if (pageSize <= 0)
            {
                pageSize = 50;
            }

            string cntSQL = string.Empty, strPageSQL = string.Empty;
            DataTable data = new DataTable();
            totalCount = 0;

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                whereStr = Regex.Replace(whereStr, @"(\s)*(where)?(\s)*(.+)", "where 1=1 and $3$4", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }

            if (!string.IsNullOrWhiteSpace(orderbyStr))
            {
                orderbyStr = Regex.Replace(orderbyStr, @"(\s)*(order)(\s)+(by)(.+)", "$5", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }
            else
            {
                throw new ArgumentNullException("orderbyStr");
            }

            cntSQL = "select count(1) from {0}  {1}";
            cntSQL = string.Format(cntSQL, joinTableName, whereStr);

            string strSQL = "select {0},ROW_NUMBER() OVER ( ORDER BY {3} ) RN from {1}  {2} ";
            strSQL = string.Format(strSQL, selColumns, joinTableName, whereStr, orderbyStr);

            strPageSQL = string.Format(@"SELECT * FROM ({0}) A WHERE   RN BETWEEN {1} AND {2}",
                                       strSQL, (currentPage - 1) * pageSize + 1, (currentPage) * pageSize);


            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    cmd.CommandText = strPageSQL;
                    PrepareCommand(cmd, conn, null, cntSQL, (object)null, 300);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);
                   
                    if (ds.Tables.Count > 0)
                    {
                        data = ds.Tables[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, data);
                    }

                    cmd.CommandText = cntSQL;
                    PrepareCommand(cmd, conn, null, cntSQL, (object)null, 300);
                    totalCount = cmd.ExecuteScalar().ChangeType<long>();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, totalCount);
                    }
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
            return data;
        }
        
        public override bool BulkCopy(DataTable data, string tableName, Dictionary<string, string> columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            List<string> lstAllColName = this.Info[tableName];
            SqlBulkCopy bulk = null;
            bulk = new SqlBulkCopy(this.ConnectionString);
            using (bulk)
            {
                if (columnMappings != null)
                {
                    foreach (var colMapping in columnMappings)
                    {
                        if (!lstAllColName.Contains(colMapping.Value, StringComparer.OrdinalIgnoreCase))
                        {
                            continue;
                        }
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(colMapping.Key, colMapping.Value));
                    }
                }
                else
                {
                    int colCount = data.Columns.Count;
                    for (int j = 0; j < colCount; j++)
                    {
                        if (!lstAllColName.Contains(data.Columns[j].ColumnName, StringComparer.OrdinalIgnoreCase))
                        {
                            data.Columns.Remove(data.Columns[j].ColumnName);
                        }
                        else
                        {
                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(data.Columns[j].ColumnName, this.Info[tableName, data.Columns[j].ColumnName].ColumnName));
                        }
                    }
                }
                bulk.DestinationTableName = tableName;
                bulk.BulkCopyTimeout = bulkCopyTimeout;
                bulk.BatchSize = batchSize;
                bulk.WriteToServer(data);
            }
            return true;
        }

        public override bool BulkCopy<P>(DataTable data, string tableName, P columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            List<string> lstAllColName = this.Info[tableName];
            SqlBulkCopy bulk = null;
            bulk = new SqlBulkCopy(this.ConnectionString);
            using (bulk)
            {
                if (columnMappings != null)
                {
                    if (TypeInfo<P>.IsAnonymousType)
                    {
                        var props = TypeInfo<P>.Props;
                        foreach (var prop in props)
                        {
                            if (!lstAllColName.Contains(prop.Name, StringComparer.OrdinalIgnoreCase))
                            {
                                continue;
                            }

                            string value = prop.GetValue(columnMappings, null).ToString();

                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(prop.Name, value));
                        }
                    }
                    else if (TypeInfo<P>.IsDict)
                    {
                        IDictionary dict = columnMappings as IDictionary;

                        foreach (DictionaryEntry kv in dict)
                        {
                            if (!lstAllColName.Contains(kv.Key.ToString(), StringComparer.OrdinalIgnoreCase))
                            {
                                continue;
                            }

                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(kv.Key.ToString(), kv.Value.ToString()));
                        }

                    }
                    else if (TypeInfo<P>.IsNameValueColl)
                    {
                        NameValueCollection nvc = columnMappings as NameValueCollection;

                        foreach (string strKey in nvc.AllKeys)
                        {
                            if (!lstAllColName.Contains(strKey, StringComparer.OrdinalIgnoreCase))
                            {
                                continue;
                            }

                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(strKey, nvc[strKey]));
                        }
                    }
                }
                else
                {
                    int colCount = data.Columns.Count;
                    for (int j = 0; j < colCount; j++)
                    {
                        if (!lstAllColName.Contains(data.Columns[j].ColumnName, StringComparer.OrdinalIgnoreCase))
                        {
                            data.Columns.Remove(data.Columns[j].ColumnName);
                        }
                        else
                        {
                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(data.Columns[j].ColumnName, this.Info[tableName, data.Columns[j].ColumnName].ColumnName));
                        }
                    }
                }
                bulk.DestinationTableName = tableName;
                bulk.BulkCopyTimeout = bulkCopyTimeout;
                bulk.BatchSize = batchSize;
                bulk.WriteToServer(data);
            }
            return true;
        }

        public override bool BulkCopy(DbDataReader reader, string tableName, Dictionary<string, string> columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            List<string> lstAllColName = this.Info[tableName];
            SqlBulkCopy bulk = null;
            bulk = new SqlBulkCopy(this.ConnectionString);
            using (bulk)
            {
                if (columnMappings != null)
                {
                    foreach (var colMapping in columnMappings)
                    {
                        if (!lstAllColName.Contains(colMapping.Value, StringComparer.OrdinalIgnoreCase))
                        {
                            continue;
                        }
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(colMapping.Key, colMapping.Value));
                    }
                }
                else
                {
                    int colCount = reader.FieldCount;
                    for (int j = 0; j < colCount; j++)
                    {
                        if (lstAllColName.Contains(reader.GetName(j), StringComparer.OrdinalIgnoreCase))
                        {
                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(reader.GetName(j), reader.GetName(j)));
                        }
                    }
                }
                bulk.DestinationTableName = tableName;
                bulk.BulkCopyTimeout = bulkCopyTimeout;
                bulk.BatchSize = batchSize;

                bulk.WriteToServer(reader);
                reader.Close();
            }
            return true;
        }

        public override bool BulkCopy<P>(DbDataReader reader, string tableName, P columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            List<string> lstAllColName = this.Info[tableName];
            SqlBulkCopy bulk = null;
            bulk = new SqlBulkCopy(this.ConnectionString);
            using (bulk)
            {
                if (columnMappings != null)
                {
                    if (TypeInfo<P>.IsAnonymousType)
                    {
                        var props = TypeInfo<P>.Props;
                        foreach (var prop in props)
                        {
                            if (!lstAllColName.Contains(prop.Name, StringComparer.OrdinalIgnoreCase))
                            {
                                continue;
                            }

                            string value = prop.GetValue(columnMappings, null).ToString();

                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(prop.Name, value));
                        }
                    }
                    else if (TypeInfo<P>.IsDict)
                    {
                        IDictionary dict = columnMappings as IDictionary;

                        foreach (DictionaryEntry kv in dict)
                        {
                            if (!lstAllColName.Contains(kv.Key.ToString(), StringComparer.OrdinalIgnoreCase))
                            {
                                continue;
                            }

                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(kv.Key.ToString(), kv.Value.ToString()));
                        }

                    }
                    else if (TypeInfo<P>.IsNameValueColl)
                    {
                        NameValueCollection nvc = columnMappings as NameValueCollection;

                        foreach (string strKey in nvc.AllKeys)
                        {
                            if (!lstAllColName.Contains(strKey, StringComparer.OrdinalIgnoreCase))
                            {
                                continue;
                            }

                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(strKey, nvc[strKey]));
                        }
                    }
                }
                else
                {
                    int colCount = reader.FieldCount;
                    for (int j = 0; j < colCount; j++)
                    {
                        if (lstAllColName.Contains(reader.GetName(j), StringComparer.OrdinalIgnoreCase))
                        {
                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(reader.GetName(j), reader.GetName(j)));
                        }
                    }
                }
                bulk.DestinationTableName = tableName;
                bulk.BulkCopyTimeout = bulkCopyTimeout;
                bulk.BatchSize = batchSize;
                bulk.WriteToServer(reader);
            }
            return true;
        }
    }
}
